# -*- coding: utf-8 -*-
# @Time: 2025/2/11 3:25
# @Author: foxhuty
# @File: deepseek_notes.py

import pandas as pd
from io import BytesIO

# 原始数据（根据用户提供内容构建DataFrame）
file_path = r'D:\data_test\高一上期末总成绩表.xlsx'

# 原始数据构建（示例数据片段，需补充完整）
data = [
    [36, "朱雨薇", "24级7班", 677.5, 102, 75, 133.5, 43, 54, 45, 70, 69, 86],
    [23, "徐子茵", "24级7班", 697, 105, 70, 130, 56, 49, 61, 73, 70, 83],
    # 补充完整所有数据行...
]

columns = ["序号", "姓名", "班级", "总分", "语文", "数学", "英语", "物理", "化学", "生物", "政治", "历史", "地理"]
# df = pd.DataFrame(data, columns=columns)
df = pd.read_excel(file_path)

# 处理可能的缺失值（示例处理周家豪的空白英语成绩）
df['英语'] = pd.to_numeric(df['英语'], errors='coerce').fillna(0)

# 中线标准设置
cutoff = {
    "语文": 86, "数学": 49, "英语": 65,
    "物理": 23, "化学": 32, "生物": 37,
    "政治": 55, "历史": 57, "地理": 61,
    "总分": 465
}

# 生成达标标记列
subjects = list(cutoff.keys())[:-1]
for subj in subjects:
    df[f'{subj}_达标'] = df[subj] >= cutoff[subj]
df['总分_达标'] = df['总分'] >= cutoff['总分']

# 班级统计计算
class_stats = df.groupby('班级').agg(
    **{f'{subj}人数': (f'{subj}_达标', 'sum') for subj in subjects},
    **{f'{subj}比例': (f'{subj}_达标', 'mean') for subj in subjects},
    总分上线人数=('总分_达标', 'sum'),
    总分上线比例=('总分_达标', 'mean')
).reset_index()

# 年级统计计算
total_stats = pd.DataFrame({
    '科目': subjects + ['总分'],
    '上线人数': [df[f'{subj}_达标'].sum() for subj in subjects] + [df['总分_达标'].sum()],
    '上线比例': [df[f'{subj}_达标'].mean() for subj in subjects] + [df['总分_达标'].mean()]
})

# Excel输出格式设置
with pd.ExcelWriter('高一期末成绩分析.xlsx', engine='openpyxl') as writer:
    # 班级统计表
    class_stats.to_excel(writer, sheet_name='班级统计', index=False)

    # 年级统计表
    total_stats.to_excel(writer, sheet_name='年级统计', index=False)

    # 获取工作表对象
    workbook = writer.book
    percent_fmt = workbook.add_format({'num_format': '0.00%'})

    # 班级统计格式设置
    worksheet = writer.sheets['班级统计']
    for idx, col in enumerate(class_stats.columns):
        if '比例' in col:
            worksheet.set_column(idx, idx, 12, percent_fmt)
        elif '人数' in col:
            worksheet.set_column(idx, idx, 10)

    # 年级统计格式设置
    worksheet = writer.sheets['年级统计']
    worksheet.set_column(0, 0, 12)
    worksheet.set_column(1, 1, 10)
    worksheet.set_column(2, 2, 12, percent_fmt)

print("分析结果已保存至：高一期末成绩分析.xlsx")
